﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

using XianShang.Common;
using XianShang.Data;
using XianShang.Entity;

namespace XianShang.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        /// <summary>
        /// 在換卡主表中插入一條新數據并返回自增ID值
        /// </summary>
        /// <param name="csInfo"></param>
        /// <returns></returns>
        public long AddNewCardSwap(CardSwapInfo csInfo)
        {
            DbParameter[] getPar ={
                                      DbHelper.MakeInParam("@area",(DbType)SqlDbType.TinyInt,3,csInfo.Area),
                                     DbHelper.MakeInParam("@cardnum",(DbType)SqlDbType.TinyInt,3,csInfo.CardNum),
                                     DbHelper.MakeInParam("@accountnum",(DbType)SqlDbType.TinyInt,3,csInfo.AccountNum),
                                     DbHelper.MakeInParam("@name",(DbType)SqlDbType.NVarChar,50,csInfo.Name),
                                      DbHelper.MakeInParam("@contacttype",(DbType)SqlDbType.NVarChar,50,csInfo.ContactType),
                                     DbHelper.MakeInParam("@tel",(DbType)SqlDbType.NVarChar,30,csInfo.Tel),
                                     DbHelper.MakeInParam("@msn",(DbType)SqlDbType.NVarChar,100,csInfo.Msn),
                                     DbHelper.MakeInParam("@bbsaccount",(DbType)SqlDbType.NVarChar,50,csInfo.BbsAccount),
                                     DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,500,csInfo.Content),
                                     DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,3,csInfo.Status),
                                     DbHelper.MakeInParam("@ip",(DbType)SqlDbType.NVarChar,50,csInfo.IP),
                                     DbHelper.MakeInParam("@time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,csInfo.Time)
                                 };          
            
            string commandText = "INSERT INTO [dbo].[XS_CardSwap]";
            commandText+=" ([Area],[CardNum],[AccountNum],[Name],[Tel],[contacttype],[Msn],[BbsAccount],[Content],[Status],[IP],[Time])";
            commandText+="VALUES(@area,@cardnum,@accountnum,@name,@tel,@contacttype,@msn,@bbsaccount,@content,@status,@ip,@time)";
            commandText+=";SELECT @@IDENTITY";

            return Convert.ToInt64(DbHelper.ExecuteScalar(CommandType.Text, commandText, getPar));
        }
        /// <summary>
        /// 新增一條數據到（换卡子表_卡片訊息）CardInfo
        /// </summary>
        /// <param name="cInfo"></param>
        /// <returns></returns>
        public int AddNewCardInfo(CardInfo cInfo)
        {
             DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@cardswapid",(DbType)SqlDbType.Int,Int32.MaxValue,cInfo.CardSwapId),
                                     DbHelper.MakeInParam("@cardtype",(DbType)SqlDbType.NVarChar,50,cInfo.CardType),
                                      DbHelper.MakeInParam("@cardnumber",(DbType)SqlDbType.NVarChar,100,cInfo.CardNumber),
                                     DbHelper.MakeInParam("@cardpassword",(DbType)SqlDbType.NVarChar,100,cInfo.CardPassword),
                                     DbHelper.MakeInParam("@time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,cInfo.Time)
                                 };          
            
            string commandText = "INSERT INTO [dbo].[XS_CardInfo]";
            commandText+=" ([CardSwapId],[CardType],[CardNumber],[CardPassword],[Time])";
            commandText+="VALUES(@cardswapid,@cardtype,@cardnumber,@cardpassword,@time)";
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,getPar);
        }
        /// <summary>
        /// 新增一條數據到（换卡子表_帳號數量）CardAccount
        /// </summary>
        /// <param name="caInfo"></param>
        /// <returns></returns>
        public int AddNewCardAccount(CardAccountInfo caInfo)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@cardswapid",(DbType)SqlDbType.Int,Int32.MaxValue,caInfo.CardSwapId),
                                     DbHelper.MakeInParam("@account",(DbType)SqlDbType.NVarChar,100,caInfo.Account),
                                     DbHelper.MakeInParam("@accounttypes",(DbType)SqlDbType.NVarChar,100,caInfo.AccountTypes),
                                     DbHelper.MakeInParam("@time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,caInfo.Time)
                                 };
            
            string commandText = "INSERT INTO [dbo].[XS_CardAccount]";
            commandText += " ([CardSwapId],[Account],[AccountTypes],[Time])";
            commandText += "VALUES(@cardswapid,@account,@accounttypes,@time)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 根據ID取得換卡信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetSwapCardById(string id)
        {
            DbParameter getPar =DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,id);

            string commandText = "SELECT  DISTINCT ";
            commandText += "[dbo].[XS_CardSwap].*,";
            commandText += "[dbo].AddCardAccountAccountClms([dbo].[XS_CardSwap].[id]) AS [Account],";
            commandText += "[dbo].AddCardAccountAccountTypesClms([dbo].[XS_CardSwap].[id]) AS [AccountTypes],";
            commandText += "[dbo].AddCardInfoCardNumberClms([dbo].[XS_CardSwap].[id]) AS [CardNumber],";
            commandText += "[dbo].AddCardInfoCardPasswordClms([dbo].[XS_CardSwap].[id]) AS [CardPassword]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_CardSwap] INNER JOIN [dbo].[XS_CardInfo] ON";
            commandText += "[dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardInfo].[CardSwapId] INNER JOIN";
            commandText += "[dbo].[XS_CardAccount] ON [dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardAccount].[ID] ";
            commandText += "WHERE [dbo].[XS_CardSwap].[id]=@id ";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar).Tables[0];
        }

        /// <summary>
        /// 查詢換卡狀態信息
        /// </summary>
        /// <param name="name"></param>
        /// <param name="tel"></param>
        /// <param name="gameAccount"></param>
        /// <param name="cardNum"></param>
        /// <returns></returns>
        public DataSet GetSwapCardAndCardAccountAndCardInfo(string name, string tel, string gameAccount, string cardNum)
        {
            #region 根據條件處理參數
            List<string> arrFiled = new List<string>();
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();


            if (!Utils.StrIsEmpty(name))
            {
                arrFiled.Add("[dbo].[XS_CardSwap].[name]");
                arrPar.Add(DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, name));
                arrCts.Add("[dbo].[XS_CardSwap].[name]=@name");
            }
            if (!Utils.StrIsEmpty(tel))
            {
                arrFiled.Add("[dbo].[XS_CardSwap].[tel]");
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 50, tel));
                arrCts.Add("[dbo].[XS_CardSwap].[tel]=@tel");
            }
            if (!Utils.StrIsEmpty(gameAccount))
            {
                arrFiled.Add("[dbo].[XS_CardSwap].[ID]");
                arrPar.Add(DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 100, gameAccount));
                arrCts.Add("[dbo].[XS_CardAccount].[Account] = @account");
            }
            if (!Utils.StrIsEmpty(cardNum))
            {
                arrFiled.Add("[dbo].[XS_CardSwap].[ID]");
                arrPar.Add(DbHelper.MakeInParam("@cardnumber", (DbType)SqlDbType.NVarChar, 100, cardNum));
                arrCts.Add("[dbo].[XS_CardInfo].[CardNumber] = @cardnumber");
            }
            arrPar.Add(DbHelper.MakeInParam("@status", (DbType)SqlDbType.TinyInt, 3, 6));
            arrCts.Add("[dbo].[XS_CardInfo].[status]<>@status");
            string filed = null, conditions = null;

            DbParameter[] getPar = new DbParameter[arrPar.Count];
            for (int iCount = 0; iCount < arrFiled.Count; ++iCount)
            {
                if (iCount == 0)
                {
                    conditions = "WHERE " + arrCts[iCount];
                }
                else
                {
                    conditions += " AND " + arrCts[iCount];
                }
                filed += "," + arrFiled[iCount];
                getPar[iCount] = arrPar[iCount];
            }

            #endregion

            string commandText = "SELECT  [dbo].[XS_CardSwap].*, ";
            commandText += "[dbo].[XS_CardAccount].[Account], [dbo].[XS_CardAccount].[AccountTypes], ";
            commandText += "[dbo].[XS_CardInfo].[CardType],[dbo].[XS_CardInfo].[CardNumber],[dbo].[XS_CardInfo].[CardPassword]";
            commandText += "FROM  [dbo].[XS_CardAccount] INNER JOIN [dbo].[XS_CardSwap] ON ";
            commandText += "[dbo].[XS_CardAccount].[CardSwapId] = [dbo].[XS_CardSwap].[ID] INNER JOIN";
            commandText += "[dbo].[XS_CardInfo] ON [dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardInfo].[CardSwapId]";
            commandText += string.Format(" {0} ", conditions);
            commandText += " ORDER BY [dbo].[XS_CardSwap].[Time] DESC";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 取得換卡列表
        /// </summary>
        /// <returns></returns>
        public IDataReader GetAllSwapCardInfo()
        {
            this.CreateSwapCardSqlFun();

            string commandText = "";
            commandText += "SELECT  DISTINCT ";
            commandText += "[dbo].[XS_CardSwap].*,";
            commandText += "[dbo].AddCardAccountAccountClms([dbo].[XS_CardSwap].[id]) AS [Account],";
            commandText += "[dbo].AddCardAccountAccountTypesClms([dbo].[XS_CardSwap].[id]) AS [AccountTypes],";
            commandText += "[dbo].AddCardInfoCardNumberClms([dbo].[XS_CardSwap].[id]) AS [CardNumber],";
            commandText += "[dbo].AddCardInfoCardPasswordClms([dbo].[XS_CardSwap].[id]) AS [CardPassword]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_CardSwap] INNER JOIN [dbo].[XS_CardInfo] ON ";
            commandText += "[dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardInfo].[CardSwapId] INNER JOIN";
            commandText += "[dbo].[XS_CardAccount] ON [dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardAccount].[CardSwapId] ";
            commandText += "WHERE [dbo].[XS_CardSwap].[Status]<>6 ";
            commandText += "ORDER BY [dbo].[XS_CardSwap].[Time] DESC";

           return DbHelper.ExecuteReader(CommandType.Text, commandText);
        }
        /// <summary>
        /// 取得換卡列表
        /// </summary>
        /// <returns></returns>
        public IDataReader GetAllSwapCardInfo(string area)
        {
            this.CreateSwapCardSqlFun();

            DbParameter getPar = DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 3, area);
            string commandText = "";
            commandText += "SELECT  DISTINCT ";
            commandText += "[dbo].[XS_CardSwap].*,";
            commandText += "[dbo].AddCardAccountAccountClms([dbo].[XS_CardSwap].[id]) AS [Account],";
            commandText += "[dbo].AddCardAccountAccountTypesClms([dbo].[XS_CardSwap].[id]) AS [AccountTypes],";
            commandText += "[dbo].AddCardInfoCardNumberClms([dbo].[XS_CardSwap].[id]) AS [CardNumber],";
            commandText += "[dbo].AddCardInfoCardPasswordClms([dbo].[XS_CardSwap].[id]) AS [CardPassword]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_CardSwap] INNER JOIN [dbo].[XS_CardInfo] ON ";
            commandText += "[dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardInfo].[CardSwapId] INNER JOIN";
            commandText += "[dbo].[XS_CardAccount] ON [dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardAccount].[CardSwapId] ";
            commandText += "WHERE [dbo].[XS_CardSwap].[Area]=@area AND [dbo].[XS_CardSwap].[Status]<>6 ";
            commandText += "ORDER BY [dbo].[XS_CardSwap].[Time] DESC";

            return DbHelper.ExecuteReader(CommandType.Text, commandText,getPar);
        }

        /// <summary>
        /// 根據實體參數和查找方式查找內容
        /// </summary>
        /// <param name="fcs"></param>
        /// <param name="SearchType">查找方式</param>
        /// <returns></returns>
        public IDataReader GetAllSwapCardAndChildTableInfo(FullCardSwapInfo fcs, int SearchType)
        {
            this.CreateSwapCardSqlFun();

            #region 根據條件處理參數
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();

            if (!Utils.StrIsEmpty(fcs.CardNumber))
            {
                arrPar.Add(DbHelper.MakeInParam("@cardnumber", (DbType)SqlDbType.NVarChar, 100, fcs.CardNumber));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_CardInfo].[cardnumber]=@cardnumber" : "[dbo].[XS_CardInfo].[cardnumber] LIKE '%'+@cardnumber+'%'");
            }
            if (!Utils.StrIsEmpty(fcs.Name))
            {
                arrPar.Add(DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, fcs.Name));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_CardSwap].[name]=@name" : "[dbo].[XS_CardSwap].[name] LIKE '%'+@name+'%'");
            }
            if (!Utils.StrIsEmpty(fcs.Tel))
            {
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 50, fcs.Tel));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_CardSwap].[tel]=@tel" : "[dbo].[XS_CardSwap].[tel] LIKE '%'+@tel+'%'");
            }
            if (!Utils.StrIsEmpty(fcs.Account))
            {
                arrPar.Add(DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 100, fcs.Account));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_CardAccount].[account]=@account" : "[dbo].[XS_CardAccount].[account] LIKE '%'+@account+'%'");
            }
            if (!Utils.StrIsEmpty(fcs.BbsAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@bbsaccount", (DbType)SqlDbType.NVarChar, 50, fcs.BbsAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_CardSwap].[bbsaccount]=@bbsaccount" : "[dbo].[XS_CardSwap].[bbsaccount] LIKE '%'+@bbsaccount+'%'");
            }
            if (!Utils.StrIsEmpty(fcs.Time))
            {
                arrPar.Add(DbHelper.MakeInParam("@time", (DbType)SqlDbType.DateTime, 50, fcs.Time));
                arrCts.Add(SearchType == 1 ? "DateDiff(DAY,[dbo].[XS_CardSwap].[time],@time)=0" : "YEAR([dbo].[XS_CardSwap].[time])=year(@time) AND MONTH([dbo].[XS_CardSwap].[time])=MONTH(@time) AND DAY([dbo].[XS_CardSwap].[time])=DAY(@time)");
            }
            if (!Utils.StrIsEmpty(fcs.Area))
            {
                arrPar.Add(DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 3, fcs.Area));
                arrCts.Add("[dbo].[XS_CardSwap].[Area]=@area");

            }

            string conditions = null;

            DbParameter[] getPar = new DbParameter[arrPar.Count];
            for (int iCount = 0; iCount < arrCts.Count; ++iCount)
            {
                conditions += " AND " + arrCts[iCount];
                getPar[iCount] = arrPar[iCount];
            }
            #endregion

            string commandText = "";
            commandText += "SELECT  DISTINCT ";
            commandText += "[dbo].[XS_CardSwap].*,";
            commandText += "[dbo].AddCardAccountAccountClms([dbo].[XS_CardSwap].[id]) AS [Account],";
            commandText += "[dbo].AddCardAccountAccountTypesClms([dbo].[XS_CardSwap].[id]) AS [AccountTypes],";
            commandText += "[dbo].AddCardInfoCardNumberClms([dbo].[XS_CardSwap].[id]) AS [CardNumber],";
            commandText += "[dbo].AddCardInfoCardPasswordClms([dbo].[XS_CardSwap].[id]) AS [CardPassword]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_CardSwap] INNER JOIN [dbo].[XS_CardInfo] ON ";
            commandText += "[dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardInfo].[CardSwapId] INNER JOIN";
            commandText += "[dbo].[XS_CardAccount] ON [dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardAccount].[CardSwapId] ";
            commandText += "WHERE [dbo].[XS_CardSwap].[Status]<>6 ";
            commandText += string.Format("{0} ",conditions);
            commandText += "ORDER BY [dbo].[XS_CardSwap].[Time] DESC";

            return DbHelper.ExecuteReader(CommandType.Text,commandText,getPar);
        }

        /// <summary>
        /// 根據實體參數和查找方式查找內容
        /// </summary>
        /// <param name="fcs"></param>
        /// <param name="SearchType">查找方式</param>
        /// <returns></returns>
        public IDataReader GetAllSwapCardAndChildTableByStatusInfo(FullCardSwapInfo fcs, int SearchType)
        {
            this.CreateSwapCardSqlFun();

            #region 根據條件處理參數
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();

            if (!Utils.StrIsEmpty(fcs.CardNumber))
            {
                arrPar.Add(DbHelper.MakeInParam("@cardnumber", (DbType)SqlDbType.NVarChar, 100, fcs.CardNumber));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_CardInfo].[cardnumber]=@cardnumber" : "[dbo].[XS_CardInfo].[cardnumber] LIKE '%'+@cardnumber+'%'");
            }
            if (!Utils.StrIsEmpty(fcs.Name))
            {
                arrPar.Add(DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, fcs.Name));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_CardSwap].[name]=@name" : "[dbo].[XS_CardSwap].[name] LIKE '%'+@name+'%'");
            }
            if (!Utils.StrIsEmpty(fcs.Tel))
            {
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 50, fcs.Tel));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_CardSwap].[tel]=@tel" : "[dbo].[XS_CardSwap].[tel] LIKE '%'+@tel+'%'");
            }
            if (!Utils.StrIsEmpty(fcs.Account))
            {
                arrPar.Add(DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 100, fcs.Account));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_CardAccount].[account]=@account" : "[dbo].[XS_CardAccount].[account] LIKE '%'+@account+'%'");
            }
            if (!Utils.StrIsEmpty(fcs.BbsAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@bbsaccount", (DbType)SqlDbType.NVarChar, 50, fcs.BbsAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_CardSwap].[bbsaccount]=@bbsaccount" : "[dbo].[XS_CardSwap].[bbsaccount] LIKE '%'+@bbsaccount+'%'");
            }
            if (!Utils.StrIsEmpty(fcs.Time))
            {
                arrPar.Add(DbHelper.MakeInParam("@time", (DbType)SqlDbType.DateTime, 50, fcs.Time));
                arrCts.Add(SearchType == 1 ? "DateDiff(DAY,[dbo].[XS_CardSwap].[time],@time)=0" : "YEAR([dbo].[XS_CardSwap].[time])=year(@time) AND MONTH([dbo].[XS_CardSwap].[time])=MONTH(@time) AND DAY([dbo].[XS_CardSwap].[time])=DAY(@time)");
            }
            if (fcs.Status >= 0)
            {
                arrPar.Add(DbHelper.MakeInParam("@status", (DbType)SqlDbType.TinyInt, 3, fcs.Status));
                arrCts.Add("[dbo].[XS_CardSwap].[status]=@status");
            }
            else
            {
                arrPar.Add(DbHelper.MakeInParam("@status", (DbType)SqlDbType.TinyInt, 3, fcs.Status));
                arrCts.Add("[dbo].[XS_CardSwap].[status]<>@status");
            }
            if (!Utils.StrIsEmpty(fcs.Area))
            {
                arrPar.Add(DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 3, fcs.Area));
                arrCts.Add("[dbo].[XS_CardSwap].[Area]=@area");
            }
            string conditions = null;

            DbParameter[] getPar = new DbParameter[arrPar.Count];
            for (int iCount = 0; iCount < arrCts.Count; ++iCount)
            {
                if(iCount==0)
                {
                    conditions += " WHERE " + arrCts[iCount];
                }else
                {
                    conditions += " AND " + arrCts[iCount];
                }
                getPar[iCount] = arrPar[iCount];
            }
            #endregion

            string commandText = "";
            commandText += "SELECT  DISTINCT ";
            commandText += "[dbo].[XS_CardSwap].*,";
            commandText += "[dbo].AddCardAccountAccountClms([dbo].[XS_CardSwap].[id]) AS [Account],";
            commandText += "[dbo].AddCardAccountAccountTypesClms([dbo].[XS_CardSwap].[id]) AS [AccountTypes],";
            commandText += "[dbo].AddCardInfoCardNumberClms([dbo].[XS_CardSwap].[id]) AS [CardNumber],";
            commandText += "[dbo].AddCardInfoCardPasswordClms([dbo].[XS_CardSwap].[id]) AS [CardPassword]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_CardSwap] INNER JOIN [dbo].[XS_CardInfo] ON ";
            commandText += "[dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardInfo].[CardSwapId] INNER JOIN";
            commandText += "[dbo].[XS_CardAccount] ON [dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardAccount].[CardSwapId] ";
            commandText += string.Format("{0} ", conditions);
            commandText += "ORDER BY [dbo].[XS_CardSwap].[Time] DESC";

            return DbHelper.ExecuteReader(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 更新Message ProcessIp Status ProcessTime四個字段
        /// </summary>
        /// <param name="cs"></param>
        /// <returns></returns>
        public int UpSwapCardMessageProcessIpStatusProcessTime(CardSwapInfo cs)
        {
            DbParameter[] getPar={
                                     DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,cs.ID),
                                     DbHelper.MakeInParam("@message",(DbType)SqlDbType.NVarChar,500,cs.Message),
                                     DbHelper.MakeInParam("@processip",(DbType)SqlDbType.NVarChar,50,cs.ProcessIP),
                                     DbHelper.MakeInParam("@processtime",(DbType)SqlDbType.DateTime,cs.ProcessTime.Length,cs.ProcessTime),
                                     DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,4,cs.Status)
                                 };
            string commandText="UPDATE [dbo].[XS_CardSwap]";
            commandText += "SET [Message] = @message,[Status] = @status,[ProcessIP] = @processip,[ProcessTime] = @processtime ";
            commandText += "WHERE [dbo].[XS_CardSwap].[id]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,getPar);
        }
        /// <summary>
        /// 更新 ProcessIp Status ProcessTime三個字段
        /// </summary>
        /// <param name="cs"></param>
        /// <returns></returns>
        public int UpSwapCardStatusProcessTimeProcessIpById(CardSwapInfo cs)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,cs.ID),
                                     DbHelper.MakeInParam("@processip",(DbType)SqlDbType.NVarChar,50,cs.ProcessIP),
                                     DbHelper.MakeInParam("@processtime",(DbType)SqlDbType.DateTime,cs.ProcessTime.Length,cs.ProcessTime),
                                     DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,4,cs.Status)
                                 };
            string commandText = "UPDATE [dbo].[XS_CardSwap]";
            commandText += "SET [Status] = @status,[ProcessIP] = @processip,[ProcessTime] = @processtime ";
            commandText += "WHERE [dbo].[XS_CardSwap].[id]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 刪除換卡信息包括子表
        /// </summary>
        /// <param name="swapcardid"></param>
        /// <returns></returns>
        public int DelSwapCardAndChildTableBySwapCardId(string swapcardid)
        {
            DbParameter getPar = DbHelper.MakeInParam("@swapcardid", (DbType)SqlDbType.Int, int.MaxValue, swapcardid);
            string commandText = "DELETE FROM [dbo].[XS_CardSwap] WHERE [dbo].[XS_CardSwap].[ID]=@swapcardid";
            int iRows = DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

            if (iRows <= 0) return 0;

            commandText = "DELETE FROM [dbo].[XS_CardAccount] WHERE [dbo].[XS_CardAccount].[CardSwapID]=@swapcardid";
            DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

            commandText = "DELETE FROM [dbo].[XS_CardInfo] WHERE [dbo].[XS_CardInfo].[CardSwapID]=@swapcardid";
            DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);

            return iRows;

        }

        /// <summary>
        /// 取得換卡主表全部信息
        /// </summary>
        /// <returns></returns>
        public IDataReader GetAllMainSwapCardInfo()
        {
            string commandText = "SELECT * FROM [dbo].[XS_CardSwap] WHERE [dbo].[XS_CardSwap].[Status]<>6 ORDER BY [dbo].[XS_CardSwap].[Time] DESC";
            return DbHelper.ExecuteReader(CommandType.Text, commandText);
        }
        /// <summary>
        /// 根據ID取得換卡主表信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetMainSwapCardInfo(string id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id);
            string commandText = "SELECT * FROM [dbo].[XS_CardSwap] WHERE [dbo].[XS_CardSwap].[id]=@id ORDER BY [dbo].[XS_CardSwap].[Time] DESC";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText,getPar).Tables[0];
        }

        /// <summary>
        /// 根據CARDSWAPID取得卡片信息
        /// </summary>
        /// <param name="swapcardid"></param>
        /// <returns></returns>
        public DataTable GetCardInfoBySwapCardId(string swapcardid)
        {
            DbParameter getPar = DbHelper.MakeInParam("@swapcardid",(DbType)SqlDbType.Int,int.MaxValue,swapcardid);
            string commandText = "SELECT * FROM [dbo].[XS_CardInfo] WHERE [dbo].[XS_CardInfo].[CardSwapId]=@swapcardid";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText,getPar).Tables[0];
        }
        /// <summary>
        /// 根據CARDSWAPID取得換卡帳號信息
        /// </summary>
        /// <param name="swapcardid"></param>
        /// <returns></returns>
        public DataTable GetCardAccountBySwapCardId(string swapcardid)
        {
            DbParameter getPar = DbHelper.MakeInParam("@swapcardid", (DbType)SqlDbType.Int, int.MaxValue, swapcardid);
            string commandText = "SELECT * FROM [dbo].[XS_CardAccount] WHERE [dbo].[XS_CardAccount].[CardSwapId]=@swapcardid";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar).Tables[0];
        }

        /// <summary>
        /// 根據主表ID取得主表和關聯字表信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetCardSwapAndChildById(string id)
        {
            this.CreateSwapCardSqlFun();

            DbParameter getPar = DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,id);
             string commandText = "";
            commandText += "SELECT  DISTINCT ";
            commandText += "[dbo].[XS_CardSwap].*,";
            commandText += "[dbo].AddCardAccountAccountClms([dbo].[XS_CardSwap].[id]) AS [Account],";
            commandText += "[dbo].AddCardAccountAccountTypesClms([dbo].[XS_CardSwap].[id]) AS [AccountTypes],";
            commandText += "[dbo].[XS_CardInfo].[CardType],[dbo].[XS_CardInfo].[CardNumber], [dbo].[XS_CardInfo].[CardPassword]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_CardSwap] INNER JOIN [dbo].[XS_CardInfo] ON ";
            commandText += "[dbo].[XS_CardSwap].[ID] = [dbo].[XS_CardInfo].[CardSwapId]";
            commandText += "WHERE [dbo].[XS_CardSwap].[id]=@id ";

           return DbHelper.ExecuteDataset(CommandType.Text, commandText,getPar).Tables[0];
             
        }

        #region 私有函數
        private void CreateSwapCardSqlFun()
        {
            #region sql自定義函數
            string sqlFun = "";
            //AddCardAccountTypeClms 用於完成都行同一列字符相加（合併一行）
            sqlFun += "IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddCardAccountAccountTypesClms]') AND xtype in (N'FN', N'IF', N'TF'))\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "execute dbo.sp_executesql @statement = N'\r\n";
            sqlFun += "Create FUNCTION [dbo].[AddCardAccountAccountTypesClms](@id nvarchar(10))\r\n ";
            sqlFun += "returns nvarchar(4000)\r\n";
            sqlFun += "AS\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "declare @str varchar(5000)\r\n";
            sqlFun += "select @str = isnull(@str + '','' , '''') +AccountTypes \r\n";
            sqlFun += "from XS_CardSwap left join XS_CardAccount on XS_CardSwap.id=XS_CardAccount.CardSwapId \r\n";
            sqlFun += "where XS_CardSwap.id = @id\r\n";
            sqlFun += "return @str\r\n";
            sqlFun += "END\r\n";
            sqlFun += "'\r\n";
            sqlFun += "END\r\n";
            sqlFun += "GO\r\n";
            //AddCardAccountAccountClms 用於完成都行同一列字符相加（合併一行）
            sqlFun += "IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddCardAccountAccountClms]') AND xtype in (N'FN', N'IF', N'TF'))\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "execute dbo.sp_executesql @statement = N'\r\n";
            sqlFun += "Create FUNCTION [dbo].[AddCardAccountAccountClms](@id nvarchar(10))\r\n ";
            sqlFun += "returns nvarchar(4000)\r\n";
            sqlFun += "AS\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "declare @str varchar(5000)\r\n";
            sqlFun += "select @str = isnull(@str + '','' , '''') +Account\r\n";
            sqlFun += "from XS_CardSwap left join XS_CardAccount on XS_CardSwap.id=XS_CardAccount.CardSwapId\r\n";
            sqlFun += "where XS_CardSwap.id = @id\r\n";
            sqlFun += "return @str\r\n";
            sqlFun += "END\r\n";
            sqlFun += "'\r\n";
            sqlFun += "END\r\n";
            sqlFun += "GO\r\n";
            //AddCardInfoCardNumberClms 用於完成都行同一列字符相加（合併一行）
            sqlFun += "IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddCardInfoCardNumberClms]') AND xtype in (N'FN', N'IF', N'TF'))\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "execute dbo.sp_executesql @statement = N'\r\n";
            sqlFun += "Create FUNCTION [dbo].[AddCardInfoCardNumberClms](@id nvarchar(10)) \r\n";
            sqlFun += "returns nvarchar(4000)\r\n";
            sqlFun += "AS\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "declare @str varchar(5000)\r\n";
            sqlFun += "select @str = isnull(@str + '','' , '''') +CardNumber \r\n";
            sqlFun += "from XS_CardSwap left join XS_CardInfo on XS_CardSwap.id=XS_CardInfo.CardSwapId\r\n";
            sqlFun += "where XS_CardSwap.id = @id\r\n";
            sqlFun += "return @str\r\n";
            sqlFun += "END\r\n";
            sqlFun += "'\r\n";
            sqlFun += "END\r\n";
            sqlFun += "GO\r\n";
            sqlFun += "";
            //AddCardInfoCardPasswordClms 用於完成都行同一列字符相加（合併一行）
            sqlFun += "IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddCardInfoCardPasswordClms]') AND xtype in (N'FN', N'IF', N'TF'))\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "execute dbo.sp_executesql @statement = N'\r\n";
            sqlFun += "Create FUNCTION [dbo].[AddCardInfoCardPasswordClms](@id nvarchar(10)) \r\n";
            sqlFun += "returns nvarchar(4000)\r\n";
            sqlFun += "AS\r\n";
            sqlFun += "BEGIN\r\n";
            sqlFun += "declare @str varchar(5000)\r\n";
            sqlFun += "select @str = isnull(@str + '','' , '''') +CardPassword\r\n ";
            sqlFun += "from XS_CardSwap left join XS_CardInfo on XS_CardSwap.id=XS_CardInfo.CardSwapId\r\n";
            sqlFun += "where XS_CardSwap.id = @id\r\n";
            sqlFun += "return @str\r\n";
            sqlFun += "END\r\n";
            sqlFun += "'\r\n";
            sqlFun += "END\r\n";
            sqlFun += "GO\r\n";
            DbHelper.ExecuteCommandWithSplitter(sqlFun);

            #endregion
        }
        #endregion
    }
}
